package persistence

import (
	"Data-API-MCP/backend/internal/domain/entity"
	"context"
	"database/sql"
	"errors"
	"fmt"
	"sync"
	"time"

	"gorm.io/gorm"
)

// DBPool 数据库连接池管理器
type DBPool struct {
	mu    sync.RWMutex
	pools map[int64]*sql.DB
	db    *gorm.DB
}

// NewDBPool 创建数据库连接池管理器
func NewDBPool(db *gorm.DB) *DBPool {
	return &DBPool{
		pools: make(map[int64]*sql.DB),
		db:    db,
	}
}

// GetDB 获取数据库连接
func (p *DBPool) GetDB(ctx context.Context, conn *entity.DBConnection) (*sql.DB, error) {
	p.mu.RLock()
	db, exists := p.pools[conn.ID]
	p.mu.RUnlock()

	if exists {
		// 测试连接是否有效
		if err := db.PingContext(ctx); err == nil {
			return db, nil
		}
		// 连接无效，关闭并移除
		db.Close()
		p.mu.Lock()
		delete(p.pools, conn.ID)
		p.mu.Unlock()
	}

	// 创建新连接
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}

	// 配置连接池
	db.SetMaxOpenConns(10)                  // 最大连接数
	db.SetMaxIdleConns(5)                   // 最大空闲连接数
	db.SetConnMaxLifetime(time.Hour)        // 连接最大生命周期
	db.SetConnMaxIdleTime(30 * time.Minute) // 空闲连接最大生命周期

	// 测试连接
	if err := db.PingContext(ctx); err != nil {
		db.Close()
		return nil, err
	}

	// 保存到连接池
	p.mu.Lock()
	p.pools[conn.ID] = db
	p.mu.Unlock()

	return db, nil
}

// CloseDB 关闭数据库连接
func (p *DBPool) CloseDB(id int64) {
	p.mu.Lock()
	defer p.mu.Unlock()

	if db, exists := p.pools[id]; exists {
		db.Close()
		delete(p.pools, id)
	}
}

// CloseAll 关闭所有数据库连接
func (p *DBPool) CloseAll() {
	p.mu.Lock()
	defer p.mu.Unlock()

	for id, db := range p.pools {
		db.Close()
		delete(p.pools, id)
	}
}

// CreateConnection 创建数据库连接
func (p *DBPool) CreateConnection(ctx context.Context, conn *entity.DBConnection) error {
	return p.db.WithContext(ctx).Create(conn).Error
}

// GetConnections 获取数据库连接列表
func (p *DBPool) GetConnections(ctx context.Context) ([]*entity.DBConnection, error) {
	var conns []*entity.DBConnection
	err := p.db.WithContext(ctx).Find(&conns).Error
	return conns, err
}

// GetConnection 获取单个数据库连接
func (p *DBPool) GetConnection(ctx context.Context, id int64) (*entity.DBConnection, error) {
	var conn entity.DBConnection
	err := p.db.WithContext(ctx).First(&conn, id).Error
	return &conn, err
}

// UpdateConnection 更新数据库连接
func (p *DBPool) UpdateConnection(ctx context.Context, conn *entity.DBConnection) error {
	return p.db.WithContext(ctx).Save(conn).Error
}

// DeleteConnection 删除数据库连接
func (p *DBPool) DeleteConnection(ctx context.Context, id int64) error {
	return p.db.WithContext(ctx).Delete(&entity.DBConnection{}, id).Error
}

// TestConnection 测试数据库连接
func (p *DBPool) TestConnection(ctx context.Context, conn *entity.DBConnection) error {
	// 判断id是否存在，如果存在则获取该条信息后再测试连接
	if conn.ID != 0 {
		conn, err := p.GetConnection(ctx, conn.ID)
		if err != nil {
			return err
		}
		// 解密密码
		conn.Password, err = DecryptPassword(conn.Password)
		if err != nil {
			return err
		}
	}
	// 依据数据库类型进行选择不同的测试方式
	switch conn.Type {
	case "mysql":
		dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
			conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)
		db, err := sql.Open("mysql", dsn)
		if err != nil {
			return err
		}
		defer db.Close()
		// 设置连接超时
		ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
		defer cancel()
		// 测试连接
		return db.PingContext(ctx)
	case "postgresql":
		dsn := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
			conn.Host, conn.Port, conn.Username, conn.Password, conn.Database)
		db, err := sql.Open("postgres", dsn)
		if err != nil {
			return err
		}
		defer db.Close()
		// 设置连接超时
		ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
		defer cancel()
		// 测试连接
		return db.PingContext(ctx)
	default:
		return errors.New("不支持的数据库类型")
	}
}

// GetTables 获取表列表
func (p *DBPool) GetTables(ctx context.Context, id int64) ([]*entity.TableInfo, error) {
	conn, err := p.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	query := `
		SELECT 
			table_name,
			table_comment,
			table_rows,
			create_time,
			update_time,
			engine,
			table_collation,
			table_schema
		FROM information_schema.tables 
		WHERE table_schema = ?
	`

	rows, err := db.QueryContext(ctx, query, conn.Database)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tables []*entity.TableInfo
	for rows.Next() {
		var table entity.TableInfo
		err := rows.Scan(
			&table.Name,
			&table.Comment,
			&table.RowCount,
			&table.CreateTime,
			&table.UpdateTime,
			&table.Engine,
			&table.Collation,
			&table.TableSchema,
		)
		if err != nil {
			return nil, err
		}
		tables = append(tables, &table)
	}

	return tables, nil
}

// GetTableInfo 获取表信息
func (p *DBPool) GetTableInfo(ctx context.Context, id int64, tableName string) (*entity.TableInfo, error) {
	conn, err := p.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	query := `
		SELECT 
			table_name,
			table_comment,
			table_rows,
			create_time,
			update_time,
			engine,
			table_collation,
			table_schema
		FROM information_schema.tables 
		WHERE table_schema = ? AND table_name = ?
	`

	var table entity.TableInfo
	err = db.QueryRowContext(ctx, query, conn.Database, tableName).Scan(
		&table.Name,
		&table.Comment,
		&table.RowCount,
		&table.CreateTime,
		&table.UpdateTime,
		&table.Engine,
		&table.Collation,
		&table.TableSchema,
	)
	if err != nil {
		return nil, err
	}

	return &table, nil
}

// GetColumns 获取列信息
func (p *DBPool) GetColumns(ctx context.Context, id int64, tableName string) ([]*entity.ColumnInfo, error) {
	conn, err := p.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	query := `
		SELECT 
			column_name,
			column_type,
			is_nullable,
			column_default,
			column_comment,
			column_key,
			extra
		FROM information_schema.columns 
		WHERE table_schema = ? AND table_name = ?
		ORDER BY ordinal_position
	`

	rows, err := db.QueryContext(ctx, query, conn.Database, tableName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var columns []*entity.ColumnInfo
	for rows.Next() {
		var column entity.ColumnInfo
		var isNullable, columnKey, extra string
		err := rows.Scan(
			&column.Name,
			&column.Type,
			&isNullable,
			&column.Default,
			&column.Comment,
			&columnKey,
			&extra,
		)
		if err != nil {
			return nil, err
		}

		column.Nullable = isNullable == "YES"
		column.IsPrimaryKey = columnKey == "PRI"
		column.IsUnique = columnKey == "UNI"
		column.IsIndex = columnKey != ""

		columns = append(columns, &column)
	}

	return columns, nil
}

// GetTableData 获取表数据
func (p *DBPool) GetTableData(ctx context.Context, id int64, tableName string, page, pageSize int) (*entity.QueryResult, error) {
	conn, err := p.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	// 获取总记录数
	var total int64
	err = db.QueryRowContext(ctx, fmt.Sprintf("SELECT COUNT(*) FROM %s", tableName)).Scan(&total)
	if err != nil {
		return nil, err
	}

	// 获取列信息
	columns, err := p.GetColumns(ctx, id, tableName)
	if err != nil {
		return nil, err
	}

	// 构建查询
	offset := (page - 1) * pageSize
	query := fmt.Sprintf("SELECT * FROM %s LIMIT %d OFFSET %d", tableName, pageSize, offset)

	// 执行查询
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	// 获取列名
	columnNames := make([]string, len(columns))
	for i, col := range columns {
		columnNames[i] = col.Name
	}

	// 读取数据
	var result entity.QueryResult
	result.Columns = columnNames
	result.Total = total

	// 准备接收数据的切片
	values := make([]interface{}, len(columns))
	valuePtrs := make([]interface{}, len(columns))
	for i := range columns {
		valuePtrs[i] = &values[i]
	}

	// 读取数据行
	for rows.Next() {
		err := rows.Scan(valuePtrs...)
		if err != nil {
			return nil, err
		}

		// 处理每一列的数据
		row := make([]interface{}, len(columns))
		for i, v := range values {
			// 处理特殊类型
			switch v.(type) {
			case []byte:
				row[i] = string(v.([]byte))
			case time.Time:
				row[i] = v.(time.Time).Format(time.RFC3339)
			default:
				row[i] = v
			}
		}
		result.Rows = append(result.Rows, row)
	}

	return &result, nil
}

// ExecuteQuery 执行查询
func (p *DBPool) ExecuteQuery(ctx context.Context, id int64, query string) (*entity.QueryResult, error) {
	conn, err := p.GetConnection(ctx, id)
	if err != nil {
		return nil, err
	}

	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		conn.Username, conn.Password, conn.Host, conn.Port, conn.Database)

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, err
	}
	defer db.Close()

	// 执行查询
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	// 获取列信息
	columns, err := rows.Columns()
	if err != nil {
		return nil, err
	}

	// 准备接收数据的切片
	values := make([]interface{}, len(columns))
	valuePtrs := make([]interface{}, len(columns))
	for i := range columns {
		valuePtrs[i] = &values[i]
	}

	// 读取数据
	var result entity.QueryResult
	result.Columns = columns
	startTime := time.Now()

	for rows.Next() {
		err := rows.Scan(valuePtrs...)
		if err != nil {
			return nil, err
		}

		// 处理每一列的数据
		row := make([]interface{}, len(columns))
		for i, v := range values {
			// 处理特殊类型
			switch v.(type) {
			case []byte:
				row[i] = string(v.([]byte))
			case time.Time:
				row[i] = v.(time.Time).Format(time.RFC3339)
			default:
				row[i] = v
			}
		}
		result.Rows = append(result.Rows, row)
	}

	result.Total = int64(len(result.Rows))
	result.Time = float64(time.Since(startTime).Milliseconds())

	return &result, nil
}
